package com.yokead.controller.tongji;

import cn.jiangzeyin.DateUtil;
import cn.jiangzeyin.StringUtil;
import cn.jiangzeyin.common.JsonMessage;
import com.alibaba.druid.util.JdbcUtils;
import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.yokead.database.DatabaseContextHolder;
import com.yokead.system.log.LogType;
import com.yokead.system.log.SystemLog;

import javax.sql.DataSource;
import java.sql.SQLException;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.*;

/**
 * Created by jiangzeyin on 2017/8/11.
 */
public class TimesUtil {

    public enum Type {
        Click, Status, ClickHours
    }

    private Type type;
    private String url;

    public TimesUtil(Type type) {
        this.type = type;
    }

    public String run(String startTime, String endTime, String dateType) throws ParseException {
        int startTime_int = 0;
        if (!StringUtil.isEmpty(startTime)) {
            startTime += " 00:00:00";
            startTime_int = (int) (DateUtil.parseTime(startTime, "yyyy-MM-dd HH:mm:ss").getTime() / 1000L);
            if (startTime_int <= 0)
                return JsonMessage.getString(140, "查询的开始时间不正确:" + startTime);
        }
        int endTime_int = 0;
        if (!StringUtil.isEmpty(endTime)) {
            endTime += " 23:59:59";
            endTime_int = (int) (DateUtil.parseTime(endTime, "yyyy-MM-dd HH:mm:ss").getTime() / 1000L);
            if (endTime_int <= 0)
                return JsonMessage.getString(140, "查询的结束时间不正确：" + endTime);
            if (startTime_int == 0)
                return JsonMessage.getString(140, "没有开始时间");
            if (endTime_int <= startTime_int)
                return JsonMessage.getString(140, "结束时间大于开始事件");

        }
        // 按照时间查询
        if (startTime_int != 0 && endTime_int != 0) {
            // 判断时间范围
            SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
            Date date;
            try {
                date = format.parse(startTime);
                Calendar calendar = Calendar.getInstance();
                calendar.setTime(date);
                if (type == Type.ClickHours)
                    calendar.add(Calendar.MONTH, 1);
                else
                    calendar.add(Calendar.MONTH, 3);
                String toEndMonth = DateUtil.formatTimeStamp("yyyyMM", (int) (calendar.getTime().getTime() / 1000L));
                String endMonth = endTime.split(" ")[0].replace("-", "").substring(0, 6);
                int toEndMonth_int = StringUtil.parseInt(toEndMonth, 0);
                int endMonth_int = StringUtil.parseInt(endMonth, 0);
                if (endMonth_int > toEndMonth_int)
                    return JsonMessage.getString(300, "时间范围请选择" + (type == Type.ClickHours ? "1" : "3") + "个月之内的");
                if (endMonth_int == toEndMonth_int) {
                    String startDay = startTime.substring(8, 10);
                    String endDay = endTime.substring(8, 10);
                    if (StringUtil.parseInt(startDay) < StringUtil.parseInt(endDay))
                        return JsonMessage.getString(300, "时间范围请选择" + (type == Type.ClickHours ? "1" : "3") + "个月之内的:-2");
                }
            } catch (Exception e1) {
                return JsonMessage.getString(140, "开始时间错误");
            }
            return doTime(url, startTime, endTime);
        }
        if (StringUtil.isEmpty(dateType) || "7".equals(dateType))
            return doNDay(url, 7);
        if ("30".equals(dateType))
            return doNDay(url, 30);
        if (type != Type.Status) {
            if ("yesterday".equals(dateType))
                return doDay(url, 1);
            if ("today".equals(dateType))
                return doDay(url, 0);
        }
        if ("toMonth".equals(dateType))
            return toMonth(url);
        return JsonMessage.getString(300, "请稍等");
    }

    public String run(String url, String startTime, String endTime, String dateType) throws ParseException {
        if (StringUtil.isEmpty(url))
            return JsonMessage.getString(100, "链接不正确");
        if (!url.startsWith("http://"))
            return JsonMessage.getString(100, "链接不正确,不是有效的http");
        String tempUrl = url.substring(url.indexOf("http://") + 7);
        int s = tempUrl.indexOf("/");
        if (s <= -1)
            return JsonMessage.getString(100, "链接不正确,-1");
        int e = tempUrl.lastIndexOf("/");
        if (s < e) {
            url = url.substring(0, e + 1 + 7);
        }
        this.url = url;
        return run(startTime, endTime, dateType);
    }

    private String doTime(String url, String startTime, String endTime) throws ParseException {
        url = transactSQLInjection(url);
        String column = null;
        if (type == Type.Click) {
            column = "FROM_UNIXTIME(time, '%m/%d')";
        } else if (type == Type.ClickHours) {
            column = "FROM_UNIXTIME(time, '%m/%d %H')";
        } else if (type == Type.Status) {
            column = "CONCAT(FROM_UNIXTIME(time, '%m/%d'),'_',STATUS)";
        }
        String sql = "SELECT  " + column + " as days,COUNT(1) daysSum FROM nginxAdLog where " +
                "time >= UNIX_TIMESTAMP('" + startTime + "') and  " +
                "time <= UNIX_TIMESTAMP('" + endTime + "')";
        if (!StringUtil.isEmpty(url))
            sql += " and url LIKE '" + url + "%'";
        sql += " GROUP BY days;";
        List<Map<String, Object>> mapList;
        try {
            SystemLog.LOG(LogType.SQL).info(sql);
            mapList = executeQuery(sql);
        } catch (Exception e1) {
            SystemLog.LOG(LogType.SQL_ERROR).error("查询异常", e1);
            return JsonMessage.getString(300, "查询异常");
        }
        if (mapList.size() < 1)
            return JsonMessage.getString(404, "没有数据");
        Map<String, Long> map = doListMap(mapList);
        if (type == Type.Status) {
            List<String> statusList = new ArrayList<>();
            map.keySet().forEach(item -> {
                String status = item.substring(6);
                if (!statusList.contains(status))
                    statusList.add(status);
            });
            int startTimeInt = (int) (DateUtil.parseTime(startTime, "yyyy-MM-dd HH:mm:ss").getTime() / 1000L);
            int endTimeInt = (int) (DateUtil.parseTime(endTime, "yyyy-MM-dd HH:mm:ss").getTime() / 1000L);
            JSONArray jsonArray = new JSONArray();
            statusList.forEach(status_item -> {
                JSONObject object = new JSONObject();
                object.put("status", status_item);
                JSONArray xArray = new JSONArray();
                JSONArray yArray = new JSONArray();
                int seep = 60 * 60 * 24;
                int count = (endTimeInt - startTimeInt) / seep;
                for (int i = 0; i <= count; i++) {
                    String dateTime = "";
                    try {
                        dateTime = getPastDate(startTime, i, type);
                    } catch (ParseException e) {
                        SystemLog.LOG(LogType.CONTROL_ERROR).error("格式化", e);
                    }
                    Long value = map.get(dateTime + "_" + status_item);
                    if (value == null)
                        value = 0L;
                    xArray.add(dateTime);
                    yArray.add(value);
                }
                object.put("x", xArray);
                object.put("y", yArray);
                jsonArray.add(object);
            });
            return JsonMessage.getString(200, "ok", jsonArray);
        }
        JSONArray xArray = new JSONArray();
        JSONArray yArray = new JSONArray();
        int startTimeInt = (int) (DateUtil.parseTime(startTime, "yyyy-MM-dd HH:mm:ss").getTime() / 1000L);
        int endTimeInt = (int) (DateUtil.parseTime(endTime, "yyyy-MM-dd HH:mm:ss").getTime() / 1000L);
        //init seep = ;
        int count = (endTimeInt - startTimeInt) / (60 * 60 * 24);
        for (int i = 0; i <= count; i++) {
            if (type == Type.ClickHours) {
                for (int h = 0; h < 24; h++) {
                    String dateTime = "";
                    try {
                        dateTime = getPastDate(startTime, i, h, type);
                    } catch (ParseException e) {
                        SystemLog.LOG(LogType.CONTROL_ERROR).error("格式化", e);
                    }
                    Long value = map.get(dateTime);
                    if (value == null)
                        value = 0L;
                    xArray.add(dateTime);
                    yArray.add(value);
                }
            } else if (type == Type.Click) {
                String dateTime = "";
                try {
                    dateTime = getPastDate(startTime, i, type);
                } catch (ParseException e) {
                    SystemLog.LOG(LogType.CONTROL_ERROR).error("格式化", e);
                }
                Long value = map.get(dateTime);
                if (value == null)
                    value = 0L;
                xArray.add(dateTime);
                yArray.add(value);
            } else {
                return JsonMessage.getString(300, "类型错误");
            }
        }
        JSONObject jsonObject = new JSONObject();
        jsonObject.put("x", xArray);
        jsonObject.put("y", yArray);
        return JsonMessage.getString(200, "ok", jsonObject);
    }

    /**
     * 本月
     *
     * @param url
     * @return
     */
    private String toMonth(String url) {
        url = transactSQLInjection(url);
        String column = null;
        if (type == Type.Click) {
            column = "FROM_UNIXTIME(time, '%d')";
        } else if (type == Type.ClickHours) {
            column = "FROM_UNIXTIME(time, '%d %H')";
        } else if (type == Type.Status) {
            column = "CONCAT(FROM_UNIXTIME(time, '%d'),'_',STATUS)";
        }
        String sql = "SELECT " + column + " as days,COUNT(1) daysSum FROM nginxAdLog where  " +
                "DATE_FORMAT(NOW(), '%Y-%m') = FROM_UNIXTIME(time, '%Y-%m') ";
        if (!StringUtil.isEmpty(url))
            sql += " and url LIKE '" + url + "%'";
        sql += " GROUP BY days;";
        //"url LIKE '" + url + "%' GROUP BY days;";
        List<Map<String, Object>> mapList;
        try {
            SystemLog.LOG(LogType.SQL).info(sql);
            mapList = executeQuery(sql);
        } catch (Exception e1) {
            SystemLog.LOG(LogType.SQL_ERROR).error("查询异常-1", e1);
            return JsonMessage.getString(300, "查询异常");
        }
        if (mapList.size() < 1)
            return JsonMessage.getString(404, "没有数据");
        Map<String, Long> map = doListMap(mapList);
        if (type == Type.Status) {
            List<String> statusList = new ArrayList<>();
            map.keySet().forEach(item -> {
                String status = item.substring(3);
                if (!statusList.contains(status))
                    statusList.add(status);
            });
            JSONArray jsonArray = new JSONArray();
            statusList.forEach(status_item -> {
                JSONObject object = new JSONObject();
                object.put("status", status_item);
                JSONArray xArray = new JSONArray();
                JSONArray yArray = new JSONArray();
                int toDay = StringUtil.parseInt(DateUtil.getCurrentFormatTime("dd"));
                for (int i = 1; i <= toDay; i++) {
                    String dateTime;
                    if (i < 10)
                        dateTime = "0" + i;
                    else
                        dateTime = i + "";
                    xArray.add(dateTime);
                    Long value = map.get(dateTime + "_" + status_item);
                    if (value == null)
                        value = 0L;
                    yArray.add(value);
                }
                object.put("x", xArray);
                object.put("y", yArray);
                jsonArray.add(object);
            });
            return JsonMessage.getString(200, "ok", jsonArray);
        }
        JSONArray xArray = new JSONArray();
        JSONArray yArray = new JSONArray();
        String doDay_ = DateUtil.getCurrentFormatTime("dd");
        int toDay = StringUtil.parseInt(doDay_);
        if (type == Type.Click) {
            for (int i = 1; i <= toDay; i++) {
                String dateTime;
                if (i < 10)
                    dateTime = "0" + i;
                else
                    dateTime = i + "";
                xArray.add(dateTime);
                Long value = map.get(dateTime);
                if (value == null)
                    value = 0L;
                yArray.add(value);
            }
            JSONObject jsonObject = new JSONObject();
            jsonObject.put("x", xArray);
            jsonObject.put("y", yArray);
            return JsonMessage.getString(200, "ok", jsonObject);
        }
        if (type == Type.ClickHours) {
            for (int i = 1; i <= toDay; i++) {
                for (int h = 0; h < 24; h++) {
                    String dateTime;
                    if (i < 10)
                        dateTime = "0" + i;
                    else
                        dateTime = i + "";
                    dateTime += " ";
                    // 追加小时
                    if (h < 10)
                        dateTime += "0" + h;
                    else
                        dateTime += h + "";
                    Long value = map.get(dateTime);
                    if (value == null)
                        value = 0L;
                    xArray.add(dateTime);
                    yArray.add(value);
                }
            }
            JSONObject jsonObject = new JSONObject();
            jsonObject.put("x", xArray);
            jsonObject.put("y", yArray);
            return JsonMessage.getString(200, "ok", jsonObject);
        }
        return JsonMessage.getString(300, "类型不正确");
    }

    /**
     * 今天和昨天
     *
     * @param url
     * @param day
     * @return
     */
    private String doDay(String url, int day) {
        url = transactSQLInjection(url);
        String column = null;
        if (type == Type.Click) {
            column = "FROM_UNIXTIME(time, '%m-%d')";
        } else if (type == Type.ClickHours) {
            column = "FROM_UNIXTIME(time, '%m-%d %H')";
        } else if (type == Type.Status) {
            column = "CONCAT(FROM_UNIXTIME(time, '%m-%d'),'_',STATUS)";
        }
        String sql = "SELECT " + column + " as days,COUNT(1) daysSum FROM nginxAdLog where  " +
                "DATE_SUB(CURDATE(), INTERVAL " + day + " DAY) = FROM_UNIXTIME(time, '%Y-%m-%d')";
        if (!StringUtil.isEmpty(url))
            sql += " and url LIKE '" + url + "%'";
        sql += " GROUP BY days;";
        List<Map<String, Object>> mapList;
        try {
            SystemLog.LOG(LogType.SQL).info(sql);
            mapList = executeQuery(sql);
        } catch (Exception e1) {
            SystemLog.LOG(LogType.SQL).error("查询异常", e1);
            return JsonMessage.getString(300, "查询异常");
        }
        if (mapList.size() < 1)
            return JsonMessage.getString(404, "没有数据");
        Map<String, Long> map = doListMap(mapList);
        if (type == Type.Status) {
            List<String> statusList = new ArrayList<>();
            map.keySet().forEach(item -> {
                String status = item.substring(6);
                if (!statusList.contains(status))
                    statusList.add(status);
            });
            JSONArray jsonArray = new JSONArray();
            statusList.forEach(status_item -> {
                JSONObject object = new JSONObject();
                object.put("status", status_item);
                JSONArray xArray = new JSONArray();
                JSONArray yArray = new JSONArray();
                String dateTime = getPastDate(day, "MM-dd");
                Long value = map.get(dateTime + "_" + status_item);
                if (value == null)
                    value = 0L;
                xArray.add(dateTime);
                yArray.add(value);
                object.put("x", xArray);
                object.put("y", yArray);
                jsonArray.add(object);
            });
            return JsonMessage.getString(200, "ok", jsonArray);
        } else {
            JSONArray xArray = new JSONArray();
            JSONArray yArray = new JSONArray();
            String dateTime = "";
            if (type == Type.ClickHours) {
                JSONObject jsonObject = new JSONObject();
                for (int h = 0; h < 24; h++) {
                    dateTime = getPastDate(day, h, "MM-dd HH");
                    Long value = map.get(dateTime);
                    if (value == null)
                        value = 0L;
                    dateTime = dateTime.substring(5);
                    xArray.add(dateTime);
                    yArray.add(value);
                }
                jsonObject.put("x", xArray);
                jsonObject.put("y", yArray);
                return JsonMessage.getString(200, "ok", jsonObject);
            }
            if (type == Type.Click) {
                dateTime = getPastDate(day, "MM-dd");
                Long value = map.get(dateTime);
                if (value == null)
                    value = 0L;
                xArray.add(dateTime);
                yArray.add(value);
                JSONObject jsonObject = new JSONObject();
                jsonObject.put("x", xArray);
                jsonObject.put("y", yArray);
                return JsonMessage.getString(200, "ok", jsonObject);
            }
            return JsonMessage.getString(300, "类型异常");
        }
    }

    /**
     * 获取指定最近指定天数数据
     *
     * @param url
     * @param day
     * @return
     */
    private String doNDay(String url, int day) {
        url = transactSQLInjection(url);
        String column = null;
        if (type == Type.Click) {
            column = "FROM_UNIXTIME(time, '%m.%d')";
        } else if (type == Type.ClickHours) {
            column = "FROM_UNIXTIME(time, '%m.%d %H')";
        } else if (type == Type.Status) {
            column = "CONCAT(FROM_UNIXTIME(time, '%m.%d'),'_',STATUS)";
        }
        String sql = "SELECT " + column + " as days,COUNT(1) daysSum FROM nginxAdLog where  " +
                "UNIX_TIMESTAMP(DATE_SUB(CURDATE(), INTERVAL " + day + " DAY)) <= time ";
        if (!StringUtil.isEmpty(url))
            sql += " and url LIKE '" + url + "%'";
        sql += " GROUP BY days;";
        List<Map<String, Object>> mapList;
        try {
            SystemLog.LOG(LogType.SQL).info(sql);
            mapList = executeQuery(sql);
        } catch (Exception e1) {
            SystemLog.LOG(LogType.SQL).error("查询异常-2", e1);
            return JsonMessage.getString(300, "查询异常");
        }
        if (mapList.size() < 1)
            return JsonMessage.getString(404, "没有数据");
        Map<String, Long> map = doListMap(mapList);
        if (type == Type.Status) {
            List<String> statusList = new ArrayList<>();
            map.keySet().forEach(item -> {
                String status = item.substring(6);
                if (!statusList.contains(status))
                    statusList.add(status);
            });
            JSONArray jsonArray = new JSONArray();
            statusList.forEach(status_item -> {
                JSONObject object = new JSONObject();
                object.put("status", status_item);
                JSONArray xArray = new JSONArray();
                JSONArray yArray = new JSONArray();
                for (int i = day - 1; i >= 0; i--) {
                    String dateTime = getPastDate(i, "MM.dd");
                    Long value = map.get(dateTime + "_" + status_item);
                    if (value == null)
                        value = 0L;
                    xArray.add(dateTime);
                    yArray.add(value);
                }
                object.put("x", xArray);
                object.put("y", yArray);
                jsonArray.add(object);
            });
            return JsonMessage.getString(200, "ok", jsonArray);
        } else {
            JSONArray xArray = new JSONArray();
            JSONArray yArray = new JSONArray();
            for (int i = day - 1; i >= 0; i--) {
                String dateTime = "";
                if (type == Type.Click) {
                    dateTime = getPastDate(i, "MM.dd");
                    Long value = map.get(dateTime);
                    if (value == null)
                        value = 0L;
                    xArray.add(dateTime);
                    yArray.add(value);
                } else if (type == Type.ClickHours) {
                    for (int h = 0; h < 24; h++) {
                        dateTime = getPastDate(i, h, "MM.dd HH");
                        Long value = map.get(dateTime);
                        if (value == null)
                            value = 0L;
                        dateTime = dateTime.substring(3);
                        xArray.add(dateTime);
                        yArray.add(value);
                    }
                }
            }
            JSONObject jsonObject = new JSONObject();
            jsonObject.put("x", xArray);
            jsonObject.put("y", yArray);
            return JsonMessage.getString(200, "ok", jsonObject);
        }
    }

    private static String transactSQLInjection(String str) {
        if (str == null)
            return "";
        return str.replaceAll(".*([';]+|(--)+).*", " ");
        // 我认为 应该是return str.replaceAll("([';])+|(--)+","");

    }

    private static Map<String, Long> doListMap(List<Map<String, Object>> mapList) {
        Map<String, Long> map = new HashMap<>();
        mapList.forEach(item -> {
            map.put((String) item.get("days"), StringUtil.parseLong(StringUtil.convertNULL(item.get("daysSum"))));
        });
        return map;
    }

    private static List<Map<String, Object>> executeQuery(String sql) throws SQLException {
        DataSource dataSource = DatabaseContextHolder.getDataSource();
        SystemLog.LOG().info(sql);
        return JdbcUtils.executeQuery(dataSource, sql);
    }

    private static String getPastDate(int past, String pattern) {
        Calendar calendar = Calendar.getInstance();
        calendar.add(Calendar.DATE, -past);
        Date today = calendar.getTime();
        SimpleDateFormat format = new SimpleDateFormat(pattern);
        return format.format(today);
    }

    private static String getPastDate(int past, int hours, String pattern) {
        Calendar calendar = Calendar.getInstance();
        calendar.add(Calendar.DATE, -past);
        if (hours > -1) {
            calendar.set(Calendar.HOUR_OF_DAY, hours);
        }
        Date today = calendar.getTime();
        SimpleDateFormat format = new SimpleDateFormat(pattern);
        return format.format(today);
    }

    private static String getPastDate(String time, int past, int hours, Type type) throws ParseException {
        SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        Date date = format.parse(time);
        Calendar calendar = Calendar.getInstance();
        calendar.setTime(date);
        if (hours > -1)
            calendar.set(Calendar.HOUR_OF_DAY, hours);
        calendar.add(Calendar.DATE, past);
        if (type == Type.Click || type == Type.Status) {
            format = new SimpleDateFormat("MM/dd");
        } else if (type == Type.ClickHours) {
            format = new SimpleDateFormat("MM/dd HH");
        }
        Date today = calendar.getTime();
        return format.format(today);
    }

    private static String getPastDate(String time, int past, Type type) throws ParseException {
        return getPastDate(time, past, -1, type);
    }
}
